﻿Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_Equipements_Utilises
    Public num As Integer = -1
    Public numf As Integer = -1
    Public modif As Integer = 0

    Public Sub vider()
        txt_Quantit_Total.Text = qt_total()
        txt_Quantité.Text = 0

        txt_remarq.Clear()
       
        If Comb_Nom_Equipement.Items.Count > 0 Then
            Comb_Nom_Equipement.SelectedIndex = 0
        End If

    End Sub
    Public Function qt_total()
        Dim a As Double = 0
        Dim b As Double = 0
        Dim c As Double = 0
        Try

            txt_Quantit_Total.Text = 0
            Cmd = New SqlCommand("select Clients_Demande_Loc.Quantite as sume from Clients_Demande_Loc,Equipement where Clients_Demande_Loc.Id_Equipement=Equipement.Id_Equipement " &
                                 "and recupere='non' and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()

            If Dr.HasRows = True Then
                Dr.Close()
                Cmd = New SqlCommand("select sum(Clients_Demande_Loc.Quantite) as sume from Clients_Demande_Loc,Equipement where Clients_Demande_Loc.Id_Equipement=Equipement.Id_Equipement and recupere='non' and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)
                Dr = Cmd.ExecuteReader()
                Dr.Read()
                a = Dr("sume")
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        Try

            txt_Quantit_Total.Text = 0
            Cmd = New SqlCommand("select sum(Quantite) as sume from Equipement where Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            If Dr.HasRows = True Then
                b = Dr("sume")
            End If


            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        Try

            txt_Quantit_Total.Text = 0
            Cmd = New SqlCommand("select Quant_Manquant  from Equipement,Equipement_Etat where Equipement.Id_Equipement=Equipement_Etat.Id_Equipement and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()

            If Dr.HasRows = True Then
                Dr.Close()
                Cmd = New SqlCommand("select sum(Quant_Manquant) as sume from Equipement,Equipement_Etat where Equipement.Id_Equipement=Equipement_Etat.Id_Equipement and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)
                Dr = Cmd.ExecuteReader()
                Dr.Read()
                c = Dr("sume")
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        Return b - c - a
    End Function
    Public Sub affiche()
        Try

            List_tous.Items.Clear()
            Cmd = New SqlCommand("select * from Equipement, Equipement_Utilise where Equipement.Id_Equipement=Equipement_Utilise.Id_Equipement and id_comande=" & id_event, Con)

            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()

                Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom_Equipement"), Dr("Type"), Dr(12), Dr(13), Dr("id_e")})
                List_tous.Items.Add(Line)

            Loop

            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Private Sub Frm_Equipements_Utilises_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txt_Quantité.Text = 0
        Try
            Comb_Nom_Equipement.Items.Clear()
            Cmd = New SqlCommand("Select distinct Nom_Equipement From Equipement", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Comb_Nom_Equipement.Items.Add(Dr("Nom_Equipement"))
            Loop
            Dr.Close()
            cnClose()
            If Comb_Nom_Equipement.Items.Count > 0 Then
                Comb_Nom_Equipement.SelectedIndex = 0
            End If

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click

        If txt_client.Text <> "" And txt_date_evenement.Text <> "" And txt_Quantité.Text <> 0 Then
            If CDbl(txt_Quantité.Text) > CDbl(txt_Quantit_Total.Text) Then
                MsgBox("La quantité de notre stock est insuffisante !", MsgBoxStyle.Critical, "Erreur !")
            Else
                If MsgBox("Vous Voulez Vraiment Ajouter ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter") = MsgBoxResult.Yes Then
                    Try
                        Try
                            Cmd = New SqlCommand("Select * From Equipement", Con)
                            open()
                            Dr = Cmd.ExecuteReader()
                            If Dr.HasRows Then
                                Do While Dr.Read()

                                    If (Dr("Nom_Equipement") = Comb_Nom_Equipement.SelectedItem.ToString And Dr("Type") = Comb_type.SelectedItem.ToString) Then
                                        num = Dr("Id_Equipement")
                                    End If
                                Loop
                            End If

                            Dr.Close()
                            cnClose()
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                            cnClose()
                        End Try


                        Cmd = New SqlCommand("insert into Equipement_Utilise (Id_Equipement,id_comande,Quantite,Remarque)" & _
                        "values (@Id_Equipement,@id_comande,@Quantite,@Remarque)", Con)

                        Cmd.Parameters.Add("@Id_Equipement", SqlDbType.Int).Value = num
                        Cmd.Parameters.Add("@id_comande", SqlDbType.Int).Value = id_event
                        Cmd.Parameters.Add("@Quantite", SqlDbType.Float).Value = txt_Quantité.Text
                        Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text

                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        MsgBox("Ajouté Avec Succes", MsgBoxStyle.Information, "Réussi !")
                        vider()
                        affiche()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            End If
        End If
    End Sub


    Private Sub Comb_Nom_Equipement_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_Nom_Equipement.SelectedIndexChanged
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If

        Try
            Comb_type.Items.Clear()
            Cmd = New SqlCommand("Select distinct Type From Equipement Where Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read
                    Comb_type.Items.Add(Dr("Type"))
                Loop
            End If

            Dr.Close()
            cnClose()

            If Comb_type.Items.Count > 0 Then
                Comb_type.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub



    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click

        With List_tous
            If .SelectedIndices.Count > 0 Then
                If MsgBox("Voulez Vous Vraiment Supprimer", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then
                    Dim n As Integer
                    For i As Integer = 0 To .SelectedIndices.Count - 1
                        n = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                    Next
                    Try
                        Cmd = New SqlCommand("Delete From Equipement_Utilise Where id_e=" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        modif = 0
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                        vider()
                        affiche()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            Else
                MsgBox("Vous n'avez selectionner aucun ligne !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With

    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        vider()
        Dim n As Integer
        txt_Quantit_Total.Text = qt_total()
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                    Comb_Nom_Equipement.Text = (.Items(.SelectedIndices(i)).SubItems(0).Text)
                    Comb_type.Text = (.Items(.SelectedIndices(i)).SubItems(1).Text)
                    txt_Quantité.Text = (.Items(.SelectedIndices(i)).SubItems(2).Text)
                    txt_remarq.Text = (.Items(.SelectedIndices(i)).SubItems(3).Text)
                Next
            End If
        End With
    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click
        If modif <> 0 Then
            If txt_client.Text <> "" And txt_date_evenement.Text <> "" And txt_Quantité.Text <> 0 Then
                If CDbl(txt_Quantité.Text) > CDbl(txt_Quantit_Total.Text) Then
                    MsgBox("La quantité de notre stock est insuffisante !", MsgBoxStyle.Critical, "Erreur !")
                Else
                    If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then

                        Try
                            Cmd = New SqlCommand("Select * From Equipement", Con)
                            open()
                            Dr = Cmd.ExecuteReader()
                            If Dr.HasRows Then
                                Do While Dr.Read()

                                    If (Dr("Nom_Equipement") = Comb_Nom_Equipement.SelectedItem.ToString And Dr("Type") = Comb_type.SelectedItem.ToString) Then
                                        num = Dr("Id_Equipement")
                                    End If
                                Loop
                            End If

                            Dr.Close()
                            cnClose()
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                            cnClose()
                        End Try

                        Try
                            Cmd = New SqlCommand("update Equipement_Utilise set Id_Equipement=@Id_Equipement, Quantite=@Quantite,Remarque=@Remarque" & _
                            " Where id_e=" & modif, Con)
                            Cmd.Parameters.Add("@Id_Equipement", SqlDbType.Int).Value = num
                            Cmd.Parameters.Add("@Quantite", SqlDbType.Float).Value = txt_Quantité.Text
                            Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text

                            open()
                            Cmd.ExecuteNonQuery()
                            cnClose()

                            modif = 0
                            MsgBox("Modification a Réussi ", MsgBoxStyle.Information, "Réussi !")
                            vider()
                            affiche()
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                            cnClose()
                        End Try
                    End If
                End If
            End If
        End If
    End Sub

    Private Sub Comb_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_type.SelectedIndexChanged
        txt_Quantit_Total.Text = qt_total()
    End Sub

    Private Sub btn_rech_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Frm_liste_Demande.MdiParent = Frm_Main
        Frm_liste_Demande.Show()
        liste = "Equi"
    End Sub

    Private Sub txt_client_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub txt_Quantité_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_Quantité.TextChanged
        Dim selStart As Integer = txt_Quantité.SelectionStart
        Dim selMoveLeft As Integer = 0
        Dim newStr As String = "" 'Build a new string by copying each valid character from the existing string. The new string starts as blank and valid characters are added 1 at a time.

        For i As Integer = 0 To txt_Quantité.Text.Length - 1

            If "0123456789,".IndexOf(txt_Quantité.Text(i)) <> -1 Then 'Characters that are in the allowed set will be added to the new string.
                newStr = newStr & txt_Quantité.Text(i)

            ElseIf i < selStart Then 'Characters that are not valid are removed - if these characters are before the cursor, we need to move the cursor left to account for their removal.
                selMoveLeft = selMoveLeft + 1

            End If
        Next

        txt_Quantité.Text = newStr 'Place the new text into the textbox.
        txt_Quantité.SelectionStart = selStart - selMoveLeft 'Move the cursor to the appropriate location.
        If txt_Quantité.Text = "" Then
            txt_Quantité.Text = 0
        End If
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Frm_liste_Demande.MdiParent = Frm_Main
        Frm_liste_Demande.Show()
        liste = "Equi"
    End Sub
End Class